﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using com.hyrentals.data;
using com.hyrentals.common;
using System.Data.SqlClient;
using ServiceStack.Text;
using System.Data;
using System.Web.Script.Serialization;

namespace com.hyrentals.dbaccess
{
    public class RentalRequestDBAccess : DBAccessBase
    {
        public RentalRequestDBAccess()
            : base(ConfigurationManager.ConnectionStrings["APPContext"].ConnectionString) { }

        public RentalRequestDBAccess(string connectionString)
            : base(connectionString) { }

        public bool InsertRentalRequest(RentalRequest rentalRequest)
        {
            bool result = true;
            try
            {
                rentalRequest.CheckInTime = DateTime.SpecifyKind(rentalRequest.CheckInTime, DateTimeKind.Local);
                rentalRequest.CheckInTime = rentalRequest.CheckInTime.ToUniversalTime();
                rentalRequest.CheckOutTime = DateTime.SpecifyKind(rentalRequest.CheckOutTime, DateTimeKind.Local);
                rentalRequest.CheckOutTime = rentalRequest.CheckOutTime.ToUniversalTime();

                string pricingJson = Serializer.ToJson(rentalRequest.Pricing);

                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_ID, DBParamNameConstant.RENTAL_REQUEST_ID_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_ID_SIZE, rentalRequest.Id),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_RENTER_ID, DBParamNameConstant.RENTAL_REQUEST_RENTER_ID_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_RENTER_ID_SIZE, rentalRequest.RenterId),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_RV_ID, DBParamNameConstant.RENTAL_REQUEST_RV_ID_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_RV_ID_SIZE, rentalRequest.RVId),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_STATE, DBParamNameConstant.RENTAL_REQUEST_STATE_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_STATE_SIZE, (byte)rentalRequest.State),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_OPEN_TIME, DBParamNameConstant.RENTAL_REQUEST_OPEN_TIME_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_OPEN_TIME_SIZE, rentalRequest.OpenTime),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_CHECKIN_TIME, DBParamNameConstant.RENTAL_REQUEST_CHECKIN_TIME_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_CHECKIN_TIME_SIZE, rentalRequest.CheckInTime),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_CHECKOUT_TIME, DBParamNameConstant.RENTAL_REQUEST_CHECKOUT_TIME_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_CHECKOUT_TIME_SIZE, rentalRequest.CheckOutTime),
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_PRICING_JSON, DBParamNameConstant.RENTAL_REQUEST_PRICING_JSON_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_PRICING_JSON_SIZE, pricingJson)
                                       };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[rr_insert]", parms);
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "InsertRentalRequest",
                    RVId = rentalRequest.RVId,
                    RenterId = rentalRequest.RenterId,
                    CheckIn = rentalRequest.CheckInTime.ToString(),
                    Checkout = rentalRequest.CheckOutTime.ToString(),
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }

        public IEnumerable<RentalRequest> GetRentalRequestsByRVOwnerId_Paging(string RVOwnerId, int page, int pageSize)
        {
            int start = (page - 1) * pageSize;
            int count = pageSize;
            return GetRentalRequestsByRVOwnerId(RVOwnerId, start, count);
        }

        public IEnumerable<RentalRequest> GetRentalRequestsByRVOwnerId(string RVOwnerId, int start, int count)
        {
            List<RentalRequest> rentalRequests = new List<RentalRequest>();
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.RV_OWNER_ID, DBParamNameConstant.RV_OWNER_ID_DB_TYPE, DBParamNameConstant.RV_OWNER_ID_SIZE, RVOwnerId),
                                            DbHelper.MakeInParam(DBParamNameConstant.START, DBParamNameConstant.START_DB_TYPE, DBParamNameConstant.START_SIZE, start),
                                            DbHelper.MakeInParam(DBParamNameConstant.COUNT, DBParamNameConstant.COUNT_DB_TYPE, DBParamNameConstant.COUNT_SIZE, count)
                                        };

                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[rr_get_by_rvownerid]", parms);

                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable RVTable = ds.Tables[0];
                    foreach (DataRow dataRow in RVTable.Rows)
                    {
                        rentalRequests.Add(ParseRR(dataRow));
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "GetRentalRequestsByRVOwnerId",
                    RVOwnerId = RVOwnerId,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return rentalRequests;
        }

        public IEnumerable<RentalRequest> GetRentalRequestsByRenterId_Paging(string renterId, int page, int pageSize)
        {
            int start = (page - 1) * pageSize;
            int count = pageSize;
            return GetRentalRequestsByRenterId(renterId, start, count);
        }

        public IEnumerable<RentalRequest> GetRentalRequestsByRenterId(string renterId, int start, int count)
        {
            List<RentalRequest> rentalRequests = new List<RentalRequest>();
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.RENTAL_REQUEST_RENTER_ID, DBParamNameConstant.RENTAL_REQUEST_RENTER_ID_DB_TYPE, DBParamNameConstant.RENTAL_REQUEST_RENTER_ID_SIZE, renterId),
                                            DbHelper.MakeInParam(DBParamNameConstant.START, DBParamNameConstant.START_DB_TYPE, DBParamNameConstant.START_SIZE, start),
                                            DbHelper.MakeInParam(DBParamNameConstant.COUNT, DBParamNameConstant.COUNT_DB_TYPE, DBParamNameConstant.COUNT_SIZE, count)
                                        };

                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[rr_get_by_renterid]", parms);

                if (ds != null && ds.Tables.Count > 0)
                {
                    DataTable RVTable = ds.Tables[0];
                    foreach (DataRow dataRow in RVTable.Rows)
                    {
                        rentalRequests.Add(ParseRR(dataRow));
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "GetRentalRequestsByRenterId",
                    RenterId = renterId,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return rentalRequests;
        }

        private RentalRequest ParseRR(DataRow dataRow)
        {
            JavaScriptSerializer jss = new JavaScriptSerializer();

            RVAvailableDateInterval[] availableDateIntervals = null;
            if (!(dataRow[DBColumnNameConstant.RV_AVAILABLE_DATE_INTERVALS_JSON] is DBNull))
            {
                availableDateIntervals = jss.Deserialize<RVAvailableDateInterval[]>((string)dataRow[DBColumnNameConstant.RV_AVAILABLE_DATE_INTERVALS_JSON]);
            }

            if (availableDateIntervals != null)
            {
                foreach (RVAvailableDateInterval dateInterval in availableDateIntervals)
                {
                    dateInterval.StartDate = DateTime.SpecifyKind(dateInterval.StartDate, DateTimeKind.Utc);
                    dateInterval.StartDate = dateInterval.StartDate.ToLocalTime();
                    dateInterval.EndDate = DateTime.SpecifyKind(dateInterval.EndDate, DateTimeKind.Utc);
                    dateInterval.EndDate = dateInterval.EndDate.ToLocalTime();
                }
            }

            RV rv =  new RV()
            {
                Id = (string)dataRow[DBColumnNameConstant.RV_ID],
                OwnerId = (string)dataRow[DBColumnNameConstant.RV_OWNER_ID],
                Title = (dataRow[DBColumnNameConstant.RV_TITLE] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_TITLE],
                RVType = (RVTypeEnum)((byte)dataRow[DBColumnNameConstant.RV_TYPE]),
                ModelStyle = (dataRow[DBColumnNameConstant.RV_MODEL_STYLE] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_MODEL_STYLE],
                MadeYear = (DateTime)dataRow[DBColumnNameConstant.RV_MADE_YEAR],
                MadeBy = (RVMadeByEnum)((byte)dataRow[DBColumnNameConstant.RV_MADE_BY]),
                Made = (dataRow[DBColumnNameConstant.RV_MADE] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_MADE],
                Sleeps = (byte)dataRow[DBColumnNameConstant.RV_SLEEPS],
                Length = (double)dataRow[DBColumnNameConstant.RV_LENGTH],
                Zip = (dataRow[DBColumnNameConstant.RV_ZIP] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_ZIP],
                StateId = (int)dataRow[DBColumnNameConstant.RV_STATE_ID],
                CityId = (int)dataRow[DBColumnNameConstant.RV_CITY_ID],
                Address = (dataRow[DBColumnNameConstant.RV_ADDRESS] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_ADDRESS],
                AvailableType = (RVAvailabilityTypeEnum)((byte)dataRow[DBColumnNameConstant.RV_AVAILABLE_TYPE]),
                AvailableDateIntervals = availableDateIntervals,
                Status = (RVStatus)((byte)dataRow[DBColumnNameConstant.RV_STATUS]),
                CoverPhotoId = (dataRow[DBColumnNameConstant.RV_COVER_PHOTO_ID] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.RV_COVER_PHOTO_ID],
                CreateTime = (DateTime)dataRow[DBColumnNameConstant.RV_CREATE_TIME]
            };

            UserBasic renter = new UserBasic()
            {
                UserId = (string)dataRow[DBColumnNameConstant.RENTAL_REQUEST_RENTER_ID],
                FirstName = (string)dataRow[DBColumnNameConstant.USER_FIRST_NAME],
                LastName = (string)dataRow[DBColumnNameConstant.USER_LAST_NAME],
                GenderType = (GenderTypeEnum)Enum.Parse(typeof(GenderTypeEnum), dataRow[DBColumnNameConstant.USER_GENDER].ToString()),
                Email = (string)dataRow[DBColumnNameConstant.USER_EMAIL],
                EmailStatus = (EmailStatusEnum)Enum.Parse(typeof(EmailStatusEnum), dataRow[DBColumnNameConstant.USER_EMAIL_STATUS].ToString()),
                AccountStatus = (AccountStatusEnum)Enum.Parse(typeof(AccountStatusEnum), dataRow[DBColumnNameConstant.USER_ACCOUNT_STATUS].ToString())
            };

            DateTime checkinTime = (DateTime)dataRow[DBColumnNameConstant.RENTAL_REQUEST_CHECKIN_TIME];
            checkinTime = DateTime.SpecifyKind(checkinTime, DateTimeKind.Utc);
            checkinTime = checkinTime.ToLocalTime();

            DateTime checkoutTime = (DateTime)dataRow[DBColumnNameConstant.RENTAL_REQUEST_CHECKOUT_TIME];
            checkoutTime = DateTime.SpecifyKind(checkoutTime, DateTimeKind.Utc);
            checkoutTime = checkoutTime.ToLocalTime();


            RentalRequest rentalRequest = new RentalRequest()
            {
                Id = (string)dataRow[DBColumnNameConstant.RENTAL_REQUEST_ID],
                RenterId = (string)(string)dataRow[DBColumnNameConstant.RENTAL_REQUEST_RENTER_ID],
                Renter = renter,
                RVId = (string)dataRow[DBColumnNameConstant.RV_ID],
                RelatedRV = rv,
                State = (RentalRequestStateType)((byte)dataRow[DBColumnNameConstant.RENTAL_REQUEST_STATE]),
                Pricing = Serializer.FromJsonTo<RVPricing>((string)dataRow[DBColumnNameConstant.RENTAL_REQUEST_PRICING_JSON]),
                OpenTime = (DateTime)dataRow[DBColumnNameConstant.RENTAL_REQUEST_OPEN_TIME],
                CloseTime = (dataRow[DBColumnNameConstant.RENTAL_REQUEST_CLOSE_TIME] is DBNull) ? null : (DateTime?)dataRow[DBColumnNameConstant.RENTAL_REQUEST_CLOSE_TIME],
                CheckInTime = checkinTime,
                CheckOutTime = checkoutTime
            };

            return rentalRequest;
        }
    }
}
